﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MediaLibraryManagement.Common;
using MediaLibraryManagement.Entity;

namespace MediaLibraryManagement.DAL
{
    public class ArtistDAL : BaseDAL
    {
        #region Properties Implementation

        protected override string TableName { get { return DatabaseTableName.Artists; } }

        #endregion

        public void Insert(Artist artist)
        {
            try
            {
                OpenConnection();
                string commandString = string.Format("INSERT INTO {0} (FirstName, LastName, DateOfBirth, Gender, Company, Country, Biography, ProfileURL, Image) "
                                                            + "VALUES (@FirstName, @LastName, @DateOfBirth, @Gender, @Company, @Country, @Biography, @ProfileURL, @Image)"
                                                    , TableName);
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    command.Parameters.AddWithValue("@FirstName", artist.FirstName);
                    command.Parameters.AddWithValue("@LastName", artist.LastName);
                    command.Parameters.AddWithValue("@DateOfBirth", artist.DateOfBirth);
                    command.Parameters.AddWithValue("@Gender", artist.Gender.ToString());
                    command.Parameters.AddWithValue("@Company", artist.Company);
                    command.Parameters.AddWithValue("@Country", artist.Country);
                    command.Parameters.AddWithValue("@Biography", artist.Biography);
                    command.Parameters.AddWithValue("@ProfileURL", artist.ProfileURL);
                    byte[] imageBytes = Utilities.ImageToByteArray(artist.Image);
                    command.Parameters.AddWithValue("@Image", imageBytes);
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        public void Update(Artist artist)
        {
            try
            {
                OpenConnection();
                string commandString = string.Format("UPDATE {0} SET "
                                                        + "FirstName = @FirstName, LastName = @LastName, DateOfBirth = @DateOfBirth, Gender = @Gender, Company = @Company, Country = @Country, Biography = @Biography, ProfileURL = @ProfileURL, Image = @Image "
                                                        + "WHERE ID = {1}"
                                                    , artist.ID
                                                    , TableName);
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    command.Parameters.AddWithValue("@FirstName", artist.FirstName);
                    command.Parameters.AddWithValue("@LastName", artist.LastName);
                    command.Parameters.AddWithValue("@DateOfBirth", artist.DateOfBirth);
                    command.Parameters.AddWithValue("@Gender", artist.Gender.ToString());
                    command.Parameters.AddWithValue("@Company", artist.Company);
                    command.Parameters.AddWithValue("@Country", artist.Country);
                    command.Parameters.AddWithValue("@Biography", artist.Biography);
                    command.Parameters.AddWithValue("@ProfileURL", artist.ProfileURL);
                    byte[] imageBytes = Utilities.ImageToByteArray(artist.Image);
                    command.Parameters.AddWithValue("@Image", imageBytes);
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        public List<Artist> Search(string name, string company, string country)
        {
            List<Artist> artists = new List<Artist>();
            try
            {
                OpenConnection();
                string commandString = string.Format("SELECT * FROM {0} "
                                                        + "WHERE LOWER((FirstName + ' ' + LastName)) LIKE '%{1}%' "
                                                        + "AND Company LIKE '%{2}%' "
                                                        + "AND Country LIKE '%{3}%'"
                                                        , TableName, name, company, country);
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Artist artist = (Artist)Fetch(reader);
                            artists.Add(artist);
                        }
                        reader.Close();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return artists;
        }

        #region Private Methods

        protected override IEntity Fetch(IDataReader reader)
        {
            Artist artist = new Artist();
            artist.ID = (Int32)reader["ID"];
            artist.FirstName = reader["FirstName"].ToString();
            artist.LastName = reader["LastName"].ToString();
            artist.DateOfBirth = (DateTime)reader["DateOfBirth"];
            artist.Gender = (Gender)Enum.Parse(typeof(Gender), reader["Gender"].ToString());
            artist.Company = reader["Company"].ToString();
            artist.Country = reader["Country"].ToString();
            artist.Biography = reader["Biography"].ToString();
            artist.ProfileURL = reader["ProfileURL"].ToString();
            artist.Image = Utilities.ByteArrayToImage((byte[])reader["Image"]);
            return artist;
        }

        #endregion Private Methods
    }
}